package modelosqlserver;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.ImageIcon;
import javax.swing.SwingUtilities;

import transacciones.ConeccionDAO;
import transacciones.TransactionDelegate;
import utilitario.FuncionesUsuario;

public class UsuarioDAO extends ConeccionDAO {

	private Usuario varUsu;
	private FuncionesUsuario funUsu;

	public UsuarioDAO(Usuario parUsu, TransactionDelegate transactionDelegate, FuncionesUsuario parFunUsu) {

		super(transactionDelegate);
		this.varUsu = parUsu;
		this.funUsu = parFunUsu;

	}// Fin de constructor

	public String agregar(Usuario parUsu) {
		String conSql;
		String resCod = "";
		PreparedStatement pstm;
		ResultSet resSql;

		try {

			conSql = "insert into usuario values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			pstm = obtenerConeccion().prepareStatement(conSql, Statement.RETURN_GENERATED_KEYS);

			pstm.setString(1, parUsu.getUsu_ide());
			pstm.setString(2, parUsu.getOrg_cod());
			pstm.setString(3, parUsu.getUsu_mail());
			pstm.setString(4, parUsu.getUsu_nombre());
			pstm.setString(5, parUsu.getUsu_apellido());
			pstm.setString(6, parUsu.getUsu_cargo());
			pstm.setString(7, parUsu.getUsu_telefono());
			pstm.setString(8, parUsu.getUsu_estado());
			pstm.setString(9, parUsu.getUsu_propietario());
			pstm.setDate(10, parUsu.getUsu_fecing());
			pstm.setDate(11, parUsu.getUsu_fecexp());
			pstm.setString(12, parUsu.getUsu_area());
			pstm.setString(13, parUsu.getUsu_clave());
			pstm.setString(14, parUsu.getUsu_logbloq());
			pstm.setString(15, parUsu.getUsu_pregsec());
			pstm.setString(16, parUsu.getUsu_ressec());
			pstm.setString(17, parUsu.getUsu_mailact());

			if (pstm.executeUpdate() == 1) {

				resCod = parUsu.getUsu_ide();

			}// Fin de if(pstm.executeUpdate() == 1)

			return resCod;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo agregar()", this.getClass().getName());
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar ORCliente

	public String actualizar(Usuario parUsu) {

		String conSql;
		String resCod = "";
		PreparedStatement pstm;
		ResultSet resSql;

		try {

			conSql = "update usuario set org_cod=?,usu_mail=?,usu_nombre=?,usu_apellido=?,usu_cargo=?,usu_telefono=?," +
					"usu_estado=?,usu_propietario=?,usu_fecing=?,usu_fecexp=?,usu_area=?,usu_clave=?,usu_logbloq=?,usu_pregsec=?," +
					"usu_ressec=?,usu_mailact=?,usu_ver where usu_ide=? and usu_ver=?";

			pstm = obtenerConeccion().prepareStatement(conSql);

			pstm.setString(1, parUsu.getOrg_cod());
			pstm.setString(2, parUsu.getUsu_mail());
			pstm.setString(3, parUsu.getUsu_nombre());
			pstm.setString(4, parUsu.getUsu_apellido());
			pstm.setString(5, parUsu.getUsu_cargo());
			pstm.setString(5, parUsu.getUsu_telefono());
			pstm.setString(6, parUsu.getUsu_estado());
			pstm.setString(7, parUsu.getUsu_propietario());
			pstm.setDate(8, parUsu.getUsu_fecing());
			pstm.setDate(9, parUsu.getUsu_fecexp());
			pstm.setString(10, parUsu.getUsu_area());
			pstm.setString(11, parUsu.getUsu_clave());
			pstm.setString(12, parUsu.getUsu_logbloq());
			pstm.setString(13, parUsu.getUsu_pregsec());
			pstm.setString(14, parUsu.getUsu_ressec());
			pstm.setString(15, parUsu.getUsu_mailact());
			pstm.setInt(16, parUsu.getUsu_ver());
			pstm.setString(17, parUsu.getUsu_ide());
			pstm.setInt(18, parUsu.getUsu_ver());

			if (pstm.executeUpdate() == 1) {

				resCod = parUsu.getUsu_ide();

			}// Fin de if(pstm.executeUpdate() == 1)

			return resCod;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo agregar()", this.getClass().getName());
			throw new RuntimeException(e);
		}

	}// Fin de Metodo Agregar ORCliente

	public Usuario obtenerUsuario(String parUsuIde, String parUsuClave) {

		String conSql;
		conSql = "select * from usuario where usu_ide='" + parUsuIde + "' and usu_clave='" + parUsuClave + "'";

		return obtenerEntidad(conSql);

	}

	public Usuario obtenerUsuario(String parUsuIde) {

		String conSql;
		conSql = "select * from usuario where usu_ide='" + parUsuIde + "'";

		return obtenerEntidad(conSql);

	}

	public Usuario obtenerEntidad(String parSql) {

		PreparedStatement pstm;
		ResultSet resSql;

		try {

			pstm = obtenerConeccion().prepareStatement(parSql);
			resSql = pstm.executeQuery();

			varUsu.limpiarInstancia();
			if (resSql.next()) {

				varUsu.setUsu_ide(resSql.getString("usu_ide"));
				varUsu.setOrg_cod(resSql.getString("org_cod"));
				varUsu.setUsu_mail(resSql.getString("usu_mail"));
				varUsu.setUsu_nombre(resSql.getString("usu_nombre"));
				varUsu.setUsu_apellido(resSql.getString("usu_apellido"));
				varUsu.setUsu_cargo(resSql.getString("usu_cargo"));
				varUsu.setUsu_telefono(resSql.getString("usu_telefono"));
				varUsu.setUsu_estado(resSql.getString("usu_estado"));
				varUsu.setUsu_propietario(resSql.getString("usu_propietario"));
				varUsu.setUsu_fecing(resSql.getDate("usu_fecing"));
				varUsu.setUsu_fecexp(resSql.getDate("usu_fecexp"));
				varUsu.setUsu_area(resSql.getString("usu_area"));
				varUsu.setUsu_clave(resSql.getString("usu_clave"));
				varUsu.setUsu_logbloq(resSql.getString("usu_logbloq"));
				varUsu.setUsu_pregsec(resSql.getString("usu_pregsec"));
				varUsu.setUsu_ressec(resSql.getString("usu_ressec"));
				varUsu.setUsu_mailact(resSql.getString("usu_mailact"));
				varUsu.setUsu_ver(resSql.getInt("usu_ver"));

			}
		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo agregar()", this.getClass().getName());
			throw new RuntimeException(e);
		}
		return varUsu;

	}

	public boolean existeUsuario(String parUsuMail, String parUsuClave) {

		String conSql;
		PreparedStatement pstm;
		ResultSet resSql;

		try {

			conSql = "select * from usuario where usu_mail = ? and usu_clave = ?";
			pstm = obtenerConeccion().prepareStatement(conSql);

			pstm.setString(1, parUsuMail);
			pstm.setString(2, parUsuClave);
			resSql = pstm.executeQuery();

			if (resSql.next()) {

				return true;

			}

			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Buscar Nombre de ORPersonal

	public boolean existeUsuario(String parUsuUsu, String parUsuPas, int parUsuCod) {

		String conSql;
		PreparedStatement pstm;
		ResultSet resSql;

		try {

			conSql = "select * from usuario where eml_usu = ? and eml_pas = ? and eml_cod <> ?";
			String otro = "select * from usuario where eml_usu = '" + parUsuUsu + "' and eml_pas = '" + parUsuPas + "' and eml_cod <> '" + parUsuCod + "'";
			pstm = obtenerConeccion().prepareStatement(conSql);

			pstm.setString(1, parUsuUsu);
			pstm.setString(2, parUsuPas);
			pstm.setInt(3, parUsuCod);
			resSql = pstm.executeQuery();

			if (resSql.next()) {

				return true;

			}

			return false;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Buscar Nombre de ORPersonal

	public List<Usuario> obtenerListaUsuario(String parUsuNom, String parUsuEst) {

		String conSql;
		try {

			if (parUsuNom.equals("TODOS")) {

				conSql = "select * from usuario where usu_estado='" + parUsuEst + "' order by usu_nombre";

			} else {

				conSql = "select * from usuario where "
						+ "(usu_nombre like '" + parUsuNom + "%' or "
						+ "usu_nombre like '%" + parUsuNom + "%') and usu_estado='" + parUsuEst + "' order by usu_nombre";

			}
			ResultSet resSql = obtenerConeccion().createStatement().executeQuery(conSql);
			List<Usuario> lisPer = new ArrayList<Usuario>();

			Usuario usu;
			while (resSql.next()) {

				usu = new Usuario();

				usu.setUsu_ide(resSql.getString("usu_ide"));
				usu.setOrg_cod(resSql.getString("org_cod"));
				usu.setUsu_mail(resSql.getString("usu_mail"));
				usu.setUsu_nombre(resSql.getString("usu_nombre"));
				usu.setUsu_apellido(resSql.getString("usu_apellido"));
				usu.setUsu_cargo(resSql.getString("usu_cargo"));
				usu.setUsu_telefono(resSql.getString("usu_telefono"));
				usu.setUsu_estado(resSql.getString("usu_estado"));
				usu.setUsu_propietario(resSql.getString("usu_propietario"));
				usu.setUsu_fecing(resSql.getDate("usu_fecing"));
				usu.setUsu_fecexp(resSql.getDate("usu_fecexp"));
				usu.setUsu_area(resSql.getString("usu_area"));
				usu.setUsu_clave(resSql.getString("usu_clave"));
				usu.setUsu_logbloq(resSql.getString("usu_logbloq"));
				usu.setUsu_pregsec(resSql.getString("usu_pregsec"));
				usu.setUsu_ressec(resSql.getString("usu_ressec"));
				usu.setUsu_mailact(resSql.getString("usu_mailact"));
				usu.setUsu_ver(resSql.getInt("usu_ver"));

				lisPer.add(usu);

			}// Fin de metodo

			return lisPer;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}// Fin de metodo obtenerListausuario(String parUsuNom)

	public ImageIcon leerBlob(byte[] parResSql) {

		ImageIcon emp_logo;

		if (parResSql != null) {
			emp_logo = new ImageIcon(parResSql);
		} else {
			emp_logo = null;
		}
		return emp_logo;
	}

	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			@Override
			public void run() {
				String conSql;
				PreparedStatement pstm;

				// Migrarusuario empMig;
				// usuarioDAO empDAO = new usuarioDAO(null, null);
				//
				// List<Migrarusuario> lisEmp = empDAO.getListMigrarusuario();
				// Iterator ite = lisEmp.iterator();
				//
				// while (ite.hasNext()) {
				//
				// empMig = (Migrarusuario) ite.next();
				//
				// try {
				// Connection conexSQLServer = ConexionSqlServer.crearConexion();
				//
				// conSql = "insert into usuario values(?,?,?,?,?)";
				// pstm = conexSQLServer.prepareStatement(conSql);
				//
				// pstm.setString(1, empMig.getEmp_cod());//Codigo
				// pstm.setString(2, empMig.getEmp_ruc());
				// pstm.setString(3, empMig.getEmp_nombre());
				// pstm.setString(4, empMig.getEmp_descripcion());
				// pstm.setString(5, empMig.getEmp_contacto());
				//
				//
				// pstm.execute();
				//
				// conexSQLServer.close();
				//
				// } catch (SQLException e) {
				// throw new RuntimeException(e);
				// }
				//
				// System.out.println("Codigo : " + empMig.getEmp_cod());
				// }
			}
		});
	}
}